package kr.hkit.summary_java.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import kr.hkit.myframework.db.ConnectionFactory;
import kr.hkit.myframework.db.DbUtil;
import kr.hkit.summary_java.erp.dto.Employee;

public class EmployeeDAO implements DaoInterface<Employee> {
	private Connection connection = ConnectionFactory.getConnection();
	
	@Override
	public void insertItem(Employee item) throws SQLException {
		String sql = "insert into employee values(?, ?, ?, ?, ?, ?)";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.setString(2, item.getEmpName());
		pstmt.setObject(3, item.getTitle()==0?null:item.getTitle());
		pstmt.setObject(4, item.getManager()==0?null:item.getManager());
		pstmt.setInt(5, item.getSalary());
		pstmt.setObject(6, item.getDeptno()==0?null:item.getDeptno());
		System.out.println(pstmt);
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public void deleteItemByNo(Employee item) throws SQLException {
		String sql = "delete from employee where empno = ?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public void updateItemByNo(Employee item) throws SQLException {
		String sql = "update employee set empname=?, title=?, manager=?, salary=?, deptno=? where empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setString(1, item.getEmpName());
		pstmt.setObject(2, item.getTitle()==0?null:item.getTitle());
		pstmt.setObject(3, item.getManager()==0?null:item.getManager());
		pstmt.setInt(4, item.getSalary());
		pstmt.setObject(5, item.getDeptno()==0?null:item.getDeptno());
		pstmt.setInt(6, item.getEmpNo());
		pstmt.executeUpdate();
		DbUtil.close(pstmt);		
	}

	@Override
	public ArrayList<Employee> listItems() throws SQLException {
		ArrayList<Employee> list = new ArrayList<>();
		String sql = "select empno, empname, title, manager, salary, deptno from employee";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		while(rs.next()){
			Employee em = new Employee();
			em.setEmpNo(rs.getInt(1));
			em.setEmpName(rs.getString(2));
			em.setTitle(rs.getInt(3));
			em.setManager(rs.getInt(4));
			em.setSalary(rs.getInt(5));
			em.setDeptno(rs.getInt(6));
			list.add(em);
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return list;
	}

	@Override
	public Employee searchItem(Employee item) throws SQLException {
		Employee emp = null;
		String sql = "select empno, empname, title, manager, salary, deptno from employee where empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, item.getEmpNo());
		ResultSet rs = pstmt.executeQuery();
		
		if (rs.next()) {
			emp = new Employee();
			emp.setEmpNo(rs.getInt(1));
			emp.setEmpName(rs.getString(2));
			emp.setTitle(rs.getInt(3));
			emp.setManager(rs.getInt(4));
			emp.setSalary(rs.getInt(5));
			emp.setDeptno(rs.getInt(6));
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return emp;
	}

/*	private int getTotalCount() throws SQLException{
		String sql = "select count(deptno) from department";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		int cnt=0;
		if (rs.next()){
			cnt = rs.getInt(1);
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return cnt;
	}*/
	
	public HashMap<String, Integer> selectedManager() throws SQLException{
		HashMap<String, Integer> hashList = new HashMap<String, Integer>();
		
		ArrayList<String> temp = new ArrayList<>();
		String sql = "select empno, titlename as title, empname , d.deptname as dept "
				+ "from employee e, code_title c, department d	"
				+ "where e.title=c.titleno and e.deptno=d.deptno";
		
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()){
			hashList.put(rs.getString("empname")+"("+rs.getString("dept")+"-"+rs.getString("title")+")", rs.getInt("empno"));
		}
		hashList.put("", 0);		
		
		String[] temps = new String[temp.size()];
		temp.toArray(temps);
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return hashList;
	}
	
	public HashMap<String, Integer> selectedDeptList() throws SQLException{
		HashMap<String, Integer> hashList = new HashMap<String, Integer>();
		
		String sql = "select deptno, deptname from department";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()){
			hashList.put(rs.getString("deptname"), rs.getInt("deptno"));
		}
		hashList.put("", 0);
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return hashList;
	}
	
	public HashMap<String, Integer> selectedTitleList() throws SQLException{
		HashMap<String, Integer> hashList = new HashMap<String, Integer>();
		
		String sql = "select titleno, titlename from code_title";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		while(rs.next()){
			hashList.put(rs.getString("titlename"), rs.getInt("titleno"));
		}
		hashList.put("", 0);
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return hashList;
	}

	public String selectedTitleById(int titleNo) throws SQLException {
		String sql = "select titlename from code_title where titleno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, titleNo);
		ResultSet rs = pstmt.executeQuery();
		String titleName=null;
		if (rs.next())
			titleName = rs.getString("titlename");
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return titleName;
	}

	public String selectedNameByEmpNo(int manager) throws SQLException {
		String sql = "select empno, titlename as title, empname , d.deptname as dept "
				+ "from employee e, code_title c, department d	"
				+ "where e.title=c.titleno and e.deptno=d.deptno and empno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, manager);
		ResultSet rs = pstmt.executeQuery();
		
		String empName=null;
		if (rs.next()){
			empName = rs.getString("empname")+"("+rs.getString("dept")+"-"+rs.getString("title")+")";
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return empName;
	}

	public String selectedDeptNameByDeptno(int deptno) throws SQLException {
		String sql = "select deptname from department where deptno=?";
		PreparedStatement pstmt = connection.prepareStatement(sql);
		pstmt.setInt(1, deptno);
		ResultSet rs = pstmt.executeQuery();
		String deptName = null;
		if (rs.next()) {
			deptName= rs.getString("deptname");
		}
		DbUtil.close(rs);
		DbUtil.close(pstmt);
		return deptName;
	}
}
